package com.huaxia.dao.excellent;

import com.huaxia.pojo.excellent.SaleDeptMDRT;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @author wangli 爱我华夏，爱我中华！
 * @date 2020/06/10 9:01
 */

public interface SaleDeptMDRTMapper {

    @Select("select a.SALEDEPTSNAME,a.saleDeptAllMDRT,b.allMDRT saleDeptLastMDRT,a.saleDeptAllMDRT-b.allMDRT saleDeptIncreaseMDRT,ROUND(DECODE(b.allMDRT,0,-9999,(a.saleDeptAllMDRT-b.allMDRT)*100/b.allMDRT),1) saleDeptYield from\n" +
            "            (select sb.SALEDEPTSNAME,NVL(a.allMDRT,0)saleDeptAllMDRT  from\n" +
            "            (select a.SALEDEPTSNAME,count(a.SALEDEPTSNAME)allMDRT from(\n" +
            "                select bcb.SALEDEPTSNAME,bcb.AGENTCODE,NVL(bcb.zcb,0)-NVL(byt.zyt,0) yearInSure from\n" +
            "            (select sb.SALEDEPTSNAME,cb.AGENTCODE,NVL(cb.yearInSure,0) zcb from\n" +
            "            (select t.SALEDEPTCODE,t.AGENTCODE, SUM(t.WRITTENSTADPREM)yearInSure from FACT_YX_PREPREM_LIST_day t where TO_CHAR(t.ISSUE_DATE,'YYYY')=TO_CHAR(SYSDATE,'YYYY') AND t.RNFLAG='N'  AND AGENTSTATE='在职' GROUP BY t.AGENTCODE,t.SALEDEPTCODE)cb RIGHT JOIN\n" +
            "            SD_SALEDEPT sb ON cb.SALEDEPTCODE=sb.SALEDEPTCODE)bcb left join\n" +
            "            (select sb.SALEDEPTSNAME,yt.AGENTCODE,NVL(yt.yearYt,0) zyt from\n" +
            "            (select t.SALEDEPTCODE,t.AGENTCODE, SUM(t.WRITTENSTADPREM)yearYt from FACT_YX_PREPREM_LIST_day t where TO_CHAR(t.YT_DATE,'YYYY')=TO_CHAR(SYSDATE,'YYYY')  AND t.RNFLAG='N'  AND AGENTSTATE='在职' GROUP BY t.AGENTCODE,t.SALEDEPTcode)yt right join\n" +
            "            SD_SALEDEPT sb ON yt.SALEDEPTCODE=sb.SALEDEPTCODE)byt on bcb.AGENTCODE=byt.AGENTCODE)a where a.yearInSure>=#{mdrtTarget} group by a.SALEDEPTSNAME)a right join\n" +
            "            SD_SALEDEPT sb ON a.SALEDEPTSNAME=sb.SALEDEPTSNAME)a,\n" +
            "\n" +
            "                (select sb.SALEDEPTSNAME,NVL(a.allMDRT,0)allMDRT  from\n" +
            "            (SELECT a.SALEDEPTSNAME,count(a.SALEDEPTSNAME)allMDRT FROM\n" +
            "            (select a.SALEDEPTSNAME,NVL(a.zcb,0)-NVL(b.zyt,0) yearInSure from\n" +
            "            (select a.SALEDEPTsname,a.AGENTCODE,a.zcb-b.zcb zcb from\n" +
            "            (select sb.SALEDEPTSNAME,cb.AGENTCODE,NVL(cb.yearInSure,0) zcb from\n" +
            "            (select t.SALEDEPTCODE,t.AGENTCODE, SUM(t.WRITTENSTADPREM)yearInSure from FACT_YX_PREPREM_LIST_day t where TO_CHAR(t.ISSUE_DATE,'YYYY')=TO_CHAR(SYSDATE,'YYYY') AND t.RNFLAG='N'  AND AGENTSTATE='在职' GROUP BY t.AGENTCODE,t.SALEDEPTCODE)cb RIGHT JOIN\n" +
            "            SD_SALEDEPT sb ON cb.SALEDEPTCODE=sb.SALEDEPTCODE)a,\n" +
            "                (select sb.SALEDEPTSNAME,cb.AGENTCODE,NVL(cb.yearInSure,0) zcb from\n" +
            "            (select t.SALEDEPTCODE,t.AGENTCODE, SUM(t.WRITTENSTADPREM)yearInSure from FACT_YX_PREPREM_LIST_day t where TO_CHAR(t.ISSUE_DATE,'YYYY')=TO_CHAR(SYSDATE,'YYYY') AND TO_CHAR(t.ISSUE_DATE,'q')=TO_CHAR(SYSDATE,'q') AND t.RNFLAG='N'  AND AGENTSTATE='在职' GROUP BY t.AGENTCODE,t.SALEDEPTCODE)cb RIGHT JOIN\n" +
            "            SD_SALEDEPT sb ON cb.SALEDEPTCODE=sb.SALEDEPTCODE)b where a.AGENTCODE=b.AGENTCODE)a left join\n" +
            "            (select a.SALEDEPTSNAME,a.AGENTCODE,a.zyt-b.zyt zyt from\n" +
            "            (select sb.SALEDEPTSNAME,yt.AGENTCODE,NVL(yt.yearYt,0) zyt from\n" +
            "            (select t.SALEDEPTCODE,t.AGENTCODE, SUM(t.WRITTENSTADPREM)yearYt from FACT_YX_PREPREM_LIST_day t where TO_CHAR(t.YT_DATE,'YYYY')=TO_CHAR(SYSDATE,'YYYY')  AND t.RNFLAG='N'  AND AGENTSTATE='在职' GROUP BY t.AGENTCODE,t.SALEDEPTcode)yt right join\n" +
            "            SD_SALEDEPT sb ON yt.SALEDEPTCODE=sb.SALEDEPTCODE)a,\n" +
            "                (select sb.SALEDEPTSNAME,yt.AGENTCODE,NVL(yt.yearYt,0) zyt from\n" +
            "            (select t.SALEDEPTCODE,t.AGENTCODE, SUM(t.WRITTENSTADPREM)yearYt from FACT_YX_PREPREM_LIST_day t where TO_CHAR(t.YT_DATE,'YYYY')=TO_CHAR(SYSDATE,'YYYY') AND TO_CHAR(t.ISSUE_DATE,'q')=TO_CHAR(SYSDATE,'q')  AND t.RNFLAG='N'  AND AGENTSTATE='在职' GROUP BY t.AGENTCODE,t.SALEDEPTcode)yt right join\n" +
            "            SD_SALEDEPT sb ON yt.SALEDEPTCODE=sb.SALEDEPTCODE)b where a.AGENTCODE=b.AGENTCODE)b on a.AGENTCODE=b.AGENTCODE)a where a.yearInSure>=#{mdrtTarget} GROUP BY a.SALEDEPTSNAME)a\n" +
            "            right join SD_SALEDEPT sb ON a.SALEDEPTSNAME=sb.SALEDEPTSNAME)b\n" +
            "            where a.SALEDEPTSNAME=b.SALEDEPTSNAME order by saleDeptIncreaseMDRT DESC\n")
    List<SaleDeptMDRT> getSaleDeptMDRT(@Param("mdrtTarget") Integer mdrtTarget);
}
